const express = require('express');
const { body, validationResult } = require('express-validator');
const { db } = require('../database/init');
const { authenticateToken, requireAdmin } = require('../middleware/auth');

const router = express.Router();

// 获取所有账单（所有用户可见）
router.get('/', authenticateToken, (req, res) => {
  const page = parseInt(req.query.page) || 1;
  const limit = parseInt(req.query.limit) || 20;
  const offset = (page - 1) * limit;

  const query = `
    SELECT b.*, u.username as creator_name 
    FROM bills b 
    JOIN users u ON b.created_by = u.id 
    ORDER BY b.date DESC, b.created_at DESC 
    LIMIT ? OFFSET ?
  `;

  db.all(query, [limit, offset], (err, bills) => {
    if (err) {
      return res.status(500).json({ error: '获取账单失败' });
    }

    // 获取总数
    db.get('SELECT COUNT(*) as total FROM bills', (err, countResult) => {
      if (err) {
        return res.status(500).json({ error: '获取账单总数失败' });
      }

      res.json({
        bills,
        pagination: {
          total: countResult.total,
          page,
          limit,
          pages: Math.ceil(countResult.total / limit)
        }
      });
    });
  });
});

// 获取单个账单详情
router.get('/:id', authenticateToken, (req, res) => {
  const { id } = req.params;

  const query = `
    SELECT b.*, u.username as creator_name 
    FROM bills b 
    JOIN users u ON b.created_by = u.id 
    WHERE b.id = ?
  `;

  db.get(query, [id], (err, bill) => {
    if (err) {
      return res.status(500).json({ error: '获取账单详情失败' });
    }
    if (!bill) {
      return res.status(404).json({ error: '账单不存在' });
    }
    res.json(bill);
  });
});

// 创建账单（仅管理员）
router.post('/', authenticateToken, requireAdmin, [
  body('title').notEmpty().withMessage('标题不能为空'),
  body('amount').isFloat({ min: 0 }).withMessage('金额必须大于0'),
  body('category').notEmpty().withMessage('分类不能为空'),
  body('date').isISO8601().withMessage('日期格式不正确')
], (req, res) => {
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    return res.status(400).json({ errors: errors.array() });
  }

  const { title, amount, category, description, date } = req.body;

  db.run(
    'INSERT INTO bills (title, amount, category, description, date, created_by) VALUES (?, ?, ?, ?, ?, ?)',
    [title, amount, category, description, date, req.user.id],
    function(err) {
      if (err) {
        return res.status(500).json({ error: '创建账单失败' });
      }
      res.json({ id: this.lastID, message: '账单创建成功' });
    }
  );
});

// 更新账单（仅管理员）
router.put('/:id', authenticateToken, requireAdmin, [
  body('title').optional().notEmpty().withMessage('标题不能为空'),
  body('amount').optional().isFloat({ min: 0 }).withMessage('金额必须大于0'),
  body('category').optional().notEmpty().withMessage('分类不能为空'),
  body('date').optional().isISO8601().withMessage('日期格式不正确')
], (req, res) => {
  const errors = validationResult(req);
  if (!errors.isEmpty()) {
    return res.status(400).json({ errors: errors.array() });
  }

  const { id } = req.params;
  const { title, amount, category, description, date } = req.body;

  const fields = [];
  const values = [];

  if (title !== undefined) {
    fields.push('title = ?');
    values.push(title);
  }
  if (amount !== undefined) {
    fields.push('amount = ?');
    values.push(amount);
  }
  if (category !== undefined) {
    fields.push('category = ?');
    values.push(category);
  }
  if (description !== undefined) {
    fields.push('description = ?');
    values.push(description);
  }
  if (date !== undefined) {
    fields.push('date = ?');
    values.push(date);
  }

  if (fields.length === 0) {
    return res.status(400).json({ error: '没有要更新的字段' });
  }

  fields.push('updated_at = CURRENT_TIMESTAMP');
  values.push(id);

  const query = `UPDATE bills SET ${fields.join(', ')} WHERE id = ?`;

  db.run(query, values, function(err) {
    if (err) {
      return res.status(500).json({ error: '更新账单失败' });
    }
    if (this.changes === 0) {
      return res.status(404).json({ error: '账单不存在' });
    }
    res.json({ message: '账单更新成功' });
  });
});

// 删除账单（仅管理员）
router.delete('/:id', authenticateToken, requireAdmin, (req, res) => {
  const { id } = req.params;

  db.run('DELETE FROM bills WHERE id = ?', [id], function(err) {
    if (err) {
      return res.status(500).json({ error: '删除账单失败' });
    }
    if (this.changes === 0) {
      return res.status(404).json({ error: '账单不存在' });
    }
    res.json({ message: '账单删除成功' });
  });
});

module.exports = router;
